import pymysql
import tkinter as tk
from tkinter import messagebox, ttk
from decimal import Decimal

# 全局唯一连接
global_conn = pymysql.connect(
    host='localhost',
    user='root',
    password='200412',
    database='keshe',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor,
    autocommit=False
)

def check_card_exists(card_id):
    conn = global_conn  # 使用全局连接
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT cardID FROM cardInfo WHERE cardID = %s", (card_id,))
            return cursor.fetchone() is not None
    except Exception:
        return False

def check_card_loss_status(card_id):
    conn = global_conn
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT IsReportLoss FROM cardInfo WHERE cardID = %s", (card_id,))
            result = cursor.fetchone()
            return result and result['IsReportLoss'] == '是'
    except Exception:
        return False

def transfer_money(from_card, to_card, amount):
    conn = global_conn  # 使用全局连接
    try:
        with conn.cursor() as cursor:
            cursor.execute("SET @is_transfer = TRUE;")
            cursor.callproc('transferMoney', (from_card, to_card, amount, ''))
            cursor.execute("SELECT @_transferMoney_3;")
            result = cursor.fetchone()
            result_msg = result.get('@_transferMoney_3', '无返回消息')

            cursor.execute("SET @is_transfer = NULL;")

        conn.commit()
    except pymysql.Error as e:
        result_msg = f"数据库错误: {e}"
        conn.rollback()
    except Exception as e:
        result_msg = f"其他错误: {e}"
    return result_msg

def update_balance(card_id, delta):
    conn = global_conn  # 使用全局连接
    try:
        with conn.cursor() as cursor:
            cursor.execute("SET @is_transfer = FALSE;")  # 设置会话变量

            cursor.execute("SELECT balance FROM cardInfo WHERE cardID = %s FOR UPDATE", (card_id,))
            row = cursor.fetchone()
            if not row:
                return "❌ 查询失败"
            old_balance = Decimal(str(row['balance']))
            new_balance = old_balance + Decimal(str(delta))
            if new_balance < 1:
                return "❌ 余额不能低于1元"
            cursor.execute("UPDATE cardInfo SET balance = %s WHERE cardID = %s", (new_balance, card_id))
        conn.commit()
        return f"✅ 操作成功，新余额为：{new_balance:.2f}"
    except Exception as e:
        conn.rollback()
        return f"❌ 数据库错误：{e}"

def main():
    root = tk.Tk()
    root.title("银行业务小程序（转账 + 存取款）")

    tk.Label(root, text="操作类型:").grid(row=0, column=0, padx=10, pady=5)
    action_var = tk.StringVar()
    action_combobox = ttk.Combobox(root, textvariable=action_var, state="readonly")
    action_combobox['values'] = ("转账", "存入", "支取")
    action_combobox.grid(row=0, column=1, padx=10, pady=5)
    action_combobox.current(0)

    tk.Label(root, text="转出卡号:").grid(row=1, column=0, padx=10, pady=5)
    from_entry = tk.Entry(root, width=25)
    from_entry.grid(row=1, column=1, padx=10, pady=5)

    to_label = tk.Label(root, text="转入卡号:")
    to_entry = tk.Entry(root, width=25)

    def update_to_entry_visibility(*args):
        action = action_var.get()
        if action == "转账":
            to_label.grid(row=2, column=0, padx=10, pady=5)
            to_entry.grid(row=2, column=1, padx=10, pady=5)
        else:
            to_label.grid_remove()
            to_entry.grid_remove()
            to_entry.delete(0, tk.END)

    action_var.trace_add('write', update_to_entry_visibility)
    update_to_entry_visibility()

    tk.Label(root, text="金额:").grid(row=3, column=0, padx=10, pady=5)
    amount_entry = tk.Entry(root, width=25)
    amount_entry.grid(row=3, column=1, padx=10, pady=5)

    def on_submit():
        action = action_var.get()
        amount_str = amount_entry.get().strip()

        if not amount_str:
            messagebox.showwarning("输入错误", "请输入金额")
            return
        try:
            amount = float(amount_str)
            if amount <= 0:
                messagebox.showwarning("输入错误", "金额必须大于0")
                return
        except ValueError:
            messagebox.showwarning("输入错误", "金额格式错误")
            return

        from_card = from_entry.get().strip()
        if not (from_card.isdigit() and len(from_card) == 16):
            messagebox.showwarning("输入错误", "转出卡号格式不正确，应为16位数字")
            return
        if not check_card_exists(from_card):
            messagebox.showwarning("输入错误", "转出卡号不存在")
            return
        if check_card_loss_status(from_card):
            messagebox.showwarning("操作禁止", "转出卡已挂失，禁止操作")
            return

        if action == "转账":
            to_card = to_entry.get().strip()
            if not (to_card.isdigit() and len(to_card) == 16):
                messagebox.showwarning("输入错误", "转入卡号格式不正确，应为16位数字")
                return
            if not check_card_exists(to_card):
                messagebox.showwarning("输入错误", "转入卡号不存在")
                return
            if check_card_loss_status(to_card):
                messagebox.showwarning("操作禁止", "转入卡已挂失，禁止转账")
                return
            if from_card == to_card:
                messagebox.showwarning("输入错误", "不能给自己转账")
                return
            result_msg = transfer_money(from_card, to_card, amount)
            messagebox.showinfo("转账结果", result_msg)
        else:
            delta = amount if action == "存入" else -amount
            result_msg = update_balance(from_card, delta)
            messagebox.showinfo("操作结果", result_msg)

    tk.Button(root, text="确认操作", command=on_submit).grid(row=4, column=0, columnspan=2, pady=15)

    def on_closing():
        global_conn.close()
        root.destroy()

    root.protocol("WM_DELETE_WINDOW", on_closing)
    root.mainloop()

if __name__ == '__main__':
    main()
